﻿using System;
using System.Collections;
using NHibernate;
using NHibernate.Cfg;
using System.Collections.Generic;

namespace Business
{
    public class Item
    {
        private ISessionFactory _sessionFactory;
        public Item()
        {
            _sessionFactory = DataObjects.Service.SessionFactory.GetInstance().GetNHibernateFactory();
        }
        
        public ArrayList List()
        {
            ArrayList arrRet = new ArrayList();
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                arrRet = (ArrayList)session.CreateCriteria(typeof(DataObjects.Item)).List();
                tx.Commit();
            }
            return arrRet;
        }

        public ArrayList GetListWithCondition(DateTime datefrom, DateTime dateto, string type, bool? status)
        {
            ArrayList arrTemp = new ArrayList();
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                string sql = "";
                if (status == null)
                    sql = "select * from Item where DATE_INPUT between ? and ? and TYPE = ? order by DATE_INPUT DESC";
                else
                    sql = "select * from Item where DATE_INPUT between ? and ? and TYPE = ? and Status = ? order by DATE_INPUT DESC";
                IQuery query = session.CreateSQLQuery(sql).AddEntity(typeof(DataObjects.Item));
                query.SetDateTime(0, datefrom);
                query.SetDateTime(1, dateto);
                query.SetString(2, type);
                if (status != null)
                    query.SetBoolean(3, status.Value);
                arrTemp = (ArrayList)query.List();
                tx.Commit();

                return arrTemp;
            }
        }

        public DataObjects.Item GetObjectByPK(int id)
        {
            ArrayList arrTemp = new ArrayList();
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                IQuery query = session.CreateSQLQuery("select top 1 * from Item where ID =?").AddEntity(typeof(DataObjects.Item));
                query.SetInt32(0, id);
                arrTemp = (ArrayList)query.List();
                tx.Commit();

                if (arrTemp.Count > 0)
                {
                    return (DataObjects.Item)arrTemp[0];
                }
                return null;
            }
        }

        public int GetMaxID()
        {
            ArrayList arrTemp = new ArrayList();
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                IQuery query = session.CreateSQLQuery("select top 1 * from Item order by ID desc").AddEntity(typeof(DataObjects.Item));
                arrTemp = (ArrayList)query.List();
                tx.Commit();

                DataObjects.Item oItem;

                if (arrTemp.Count > 0)
                {
                    oItem = (DataObjects.Item)arrTemp[0];
                    if (oItem != null)
                    {
                        return oItem.Id;
                    }
                }
                return 0;
            }
        }

        //code insert - update - delete 
        public void Insert(DataObjects.Item oItem)
        {
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                session.Save(oItem);
                tx.Commit();			        
            }
        }
        public void Update(DataObjects.Item oItem)
        {
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                session.Update(oItem);
                tx.Commit();		        
            }
        }
        public void DeleteByPK(int id)
        {
            using (ISession session = _sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                DataObjects.Item oItem = (DataObjects.Item)session.Load(typeof(DataObjects.Item), id);
                session.Delete(oItem);
                tx.Commit();
            }
        }
    }
}
